package csu.web.mypetstore.persistence.impl;

import csu.web.mypetstore.domain.Item;
import csu.web.mypetstore.domain.LineItem;
import csu.web.mypetstore.domain.Order;
import csu.web.mypetstore.persistence.DBUtil;
import csu.web.mypetstore.persistence.LineItemDao;
import csu.web.mypetstore.persistence.OrderDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static java.lang.System.out;

public class OrderDaoImpl implements OrderDao {
    private static final String getOrderString = "select BILLADDR1 AS billAddress1, BILLADDR2 AS billAddress2, BILLCITY,BILLCOUNTRY, BILLSTATE, BILLTOFIRSTNAME, BILLTOLASTNAME, BILLZIP, SHIPADDR1 AS shipAddress1,SHIPADDR2 AS shipAddress2, SHIPCITY, SHIPCOUNTRY, SHIPSTATE, SHIPTOFIRSTNAME, SHIPTOLASTNAME, SHIPZIP, CARDTYPE, COURIER, CREDITCARD, EXPRDATE AS expiryDate, LOCALE, ORDERDATE, ORDERS.ORDERID, TOTALPRICE, USERID AS username FROM ORDERS WHERE ORDERID = ? ";
    private static final String getOrderByUserNameString = "SELECT BILLADDR1 AS billAddress1, BILLADDR2 AS billAddress2, BILLCITY, BILLCOUNTRY, BILLSTATE, BILLTOFIRSTNAME, BILLTOLASTNAME, BILLZIP, SHIPADDR1 AS shipAddress1, SHIPADDR2 AS shipAddress2, SHIPCITY, SHIPCOUNTRY, SHIPSTATE, SHIPTOFIRSTNAME, SHIPTOLASTNAME, SHIPZIP, CARDTYPE, COURIER, CREDITCARD, EXPRDATE AS expiryDate, LOCALE, ORDERDATE, ORDERS.ORDERID, TOTALPRICE, USERID AS username FROM ORDERS WHERE USERID = ? ";
    private static final String insertOrder ="INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY, COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME, CREDITCARD, EXPRDATE, CARDTYPE, LOCALE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String insertOrderStatus = "INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)" +
            " ?, ?,?, " +
            "?";
    @Override
    public List<Order> getOrdersByUserid(String userid) {
        List<Order> orders = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(getOrderByUserNameString);
            preparedStatement.setString(1,userid);
            ResultSet resultSet = preparedStatement.executeQuery();

//            out.println("readytogetorderlist userid:"+userid);

            while (resultSet.next()){
//                out.println("while!");
                orders.add(resultSetToOrder(resultSet));
            }
            DBUtil.closeConnection(connection);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return orders;
    }

    @Override
    public Order getOrder(int orderId) {
        Order order = new Order();
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(getOrderString);
            preparedStatement.setInt(1,orderId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                order = resultSetToOrder(resultSet);
            }
            DBUtil.closeConnection(connection);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return order;
    }

    @Override
    public void insertOrderStatus(Order order) {
        System.out.println("别用这个函数！");

//        try {
//            Connection connection = DBUtil.getConnection();
//            PreparedStatement preparedStatement = connection.prepareStatement(insertOrderStatus);
//
//            preparedStatement.setInt(1,order.getOrderId());
//            preparedStatement.setString(2,order.get);
//            preparedStatement.setDate(3, (Date) order.getOrderDate());
//            preparedStatement.setString(4,order.getShipAddress1());
//
//            DBUtil.closeConnection(connection);
//            DBUtil.closeStatement(preparedStatement);
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }

    }

    @Override
    public void insertOrder(Order order) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(insertOrder);
//            ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, " +
//            "SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY," +
//                    " COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME, CREDITCARD," +
//                    " EXPRDATE, CARDTYPE, LOCALE

            preparedStatement.setInt(1,order.getOrderId());
            preparedStatement.setString(2,order.getUsername());
            preparedStatement.setDate(3,new java.sql.Date(order.getOrderDate().getTime()));
            preparedStatement.setString(4,order.getShipAddress1());
            preparedStatement.setString(5,order.getShipAddress2());
            preparedStatement.setString(6,order.getShipCity());
            preparedStatement.setString(7,order.getShipState());
            preparedStatement.setString(8,order.getShipZip());
            preparedStatement.setString(9,order.getShipCountry());
            preparedStatement.setString(10,order.getBillAddress1());
            preparedStatement.setString(11,order.getBillAddress2());
            preparedStatement.setString(12,order.getBillCity());
            preparedStatement.setString(13,order.getBillState());
            preparedStatement.setString(14,order.getBillZip());
            preparedStatement.setString(15,order.getBillCountry());
            preparedStatement.setString(16,order.getCourier());
            preparedStatement.setBigDecimal(17,order.getTotalPrice());
            preparedStatement.setString(18,order.getBillToFirstName());
            preparedStatement.setString(19,order.getBillToLastName());
            preparedStatement.setString(20, order.getShipToFirstName());
            preparedStatement.setString(21,order.getShipToLastName());
            preparedStatement.setString(22,order.getCreditCard());
            preparedStatement.setString(23,order.getExpiryDate());
            preparedStatement.setString(24,order.getCardType());
            preparedStatement.setString(25,order.getLocale());

            int isSucceed = preparedStatement.executeUpdate();
            if(isSucceed>0){
                out.println("inserOrder!");
            }else{
                out.println("inserFalse!");
            }

            DBUtil.closeConnection(connection);
            DBUtil.closeStatement(preparedStatement);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private Order resultSetToOrder(ResultSet resultSet) throws SQLException {
        Order order = new Order();

        order.setBillAddress1(resultSet.getString("billAddress1"));
        order.setBillCity(resultSet.getString("billcity"));
        order.setBillAddress2(resultSet.getString("billAddress2"));
        order.setBillCountry(resultSet.getString("billcountry"));
        order.setBillState(resultSet.getString("billstate"));
        order.setCourier(resultSet.getString("courier"));
        order.setOrderDate(resultSet.getDate("orderdate"));
        order.setBillZip(resultSet.getString("billzip"));
        order.setCardType(resultSet.getString("cardtype"));
        order.setCreditCard(resultSet.getString("creditcard"));
        order.setLocale(resultSet.getString("locale"));
        order.setOrderId(resultSet.getInt("orderid"));
        order.setUsername(resultSet.getString("username"));
        order.setExpiryDate(resultSet.getString("expirydate"));
        order.setShipZip(resultSet.getString("shipzip"));
        order.setShipCity(resultSet.getString("shipcity"));
        order.setShipState(resultSet.getString("shipstate"));
        order.setShipCountry(resultSet.getString("shipcountry"));
        order.setBillToFirstName(resultSet.getString("billtofirstname"));
        order.setBillToLastName(resultSet.getString("billtolastname"));
        order.setTotalPrice(resultSet.getBigDecimal("totalprice"));
        order.setShipAddress1(resultSet.getString("shipAddress1"));
        order.setShipAddress2(resultSet.getString("shipAddress2"));
        order.setShipToLastName(resultSet.getString("shiptolastname"));
        order.setShipToFirstName(resultSet.getString("shiptofirstname"));

        return order;
    }
}
